﻿--EXEC SP_LOAD_OPEN_PO_REPORT 0
ALTER PROCEDURE [SP_LOAD_OPEN_PO_REPORT]
(
	@DUMMY INT
		
)
AS
BEGIN
		DECLARE @PO_ID INT, @PROCESSSTEP_ID INT, @VENDOR_ID INT, @Order_QTY INT, @Shipped_QTY INT
		DECLARE @Cancelled_Qty INT , @QTY_STARTED INT, @BALANCE_QTY INT,@PO_COST NUMERIC(18,4)
		DECLARE @PO_NO	VARCHAR(50), @V_NAME VARCHAR(50),@PART_NO VARCHAR(50), @PO_DATE DATETIME
		DECLARE @POLYLAYERS VARCHAR(50), @EPITAXIAL VARCHAR(50), @NOOFMASKLAYERS VARCHAR(50), @MASKSERVICETYPE VARCHAR(50), @PROCESSGEOMETRY VARCHAR(50),
				@PROCESS VARCHAR(50), @WaferSIZE  VARCHAR(50), @METALLAYERS  VARCHAR(50)	
			
		CREATE TABLE #RESULTS ( [Vendor Name] VARCHAR(50),[Part Number] VARCHAR(50),[PO No.] VARCHAR(50),[PO Date] DATETIME,[Ordered Qty] INT,
		[Shipped Qty] INT, [Cancelled Qty] INT,	[Qty Started] INT,[Balance Qty] INT, [PO Rate] NUMERIC(18,4), [PO Amount] NUMERIC(18,4),
		[Mask Service Type] VARCHAR(50), [Process Geometry] VARCHAR(50), [Process] VARCHAR(50),	[Wafer Size (Inch)] VARCHAR(50),[Metal Layers] VARCHAR(50),	
		[Poly Layers] VARCHAR(50),[Epitaxial] VARCHAR(50),[# of Mask Layers] VARCHAR(50) )
			
		DECLARE @PO_CURSOR CURSOR 
		SET @PO_CURSOR = CURSOR FOR 
		SELECT POT.PURCHASEORDER_ID, PROCESSSTEP_ID, VENDOR_ID, Sum(Requested_Qty) FROM PurchaseOrder_Table POT, PurchaseOrder_Line_Item_Table POLIT WHERE POT.PURCHASEORDER_ID = POLIT.PURCHASEORDER_ID
				 AND POLIT.STATUS = 'APPROVED'
				Group By POT.PURCHASEORDER_ID, PROCESSSTEP_ID,VENDOR_ID 

		OPEN @PO_CURSOR
		FETCH NEXT FROM @PO_CURSOR INTO @PO_ID , @PROCESSSTEP_ID, @VENDOR_ID,@Order_QTY
		WHILE @@FETCH_STATUS = 0
		BEGIN
				IF @Order_QTY > 0
				BEGIN
					SELECT @V_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = @VENDOR_ID	
					SELECT @PART_NO = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
					SELECT @PO_NO = PURCHASEORDER_NO, @PO_DATE = PO_DATE FROM PurchaseOrder_Table WHERE PURCHASEORDER_ID = @PO_ID
					
					SELECT @PO_COST = UnitCost FROM PurchaseOrder_Table POT, PurchaseOrder_Line_Item_Table POLIT WHERE POT.PURCHASEORDER_ID = POLIT.PURCHASEORDER_ID 
							AND POT.PURCHASEORDER_ID = @PO_ID AND PROCESSSTEP_ID = @PROCESSSTEP_ID
							AND VENDOR_ID = @VENDOR_ID

					SELECT @Shipped_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID= LT.LOT_ID AND PROCESSSTEP_ID = @PROCESSSTEP_ID 
									AND LOCATION_ID = (SELECT LOCATION_ID FROM LOCATION WHERE L_TYPE = 'FAB' AND VENDOR_ID = @VENDOR_ID ) AND SL_CUSTOMER_PO = @PO_NO

					SELECT @Cancelled_Qty = Sum(Requested_Qty) FROM PurchaseOrder_Table POT, PurchaseOrder_Line_Item_Table POLIT WHERE POT.PURCHASEORDER_ID = POLIT.PURCHASEORDER_ID 
							AND POT.PURCHASEORDER_ID = @PO_ID AND PROCESSSTEP_ID = @PROCESSSTEP_ID
							AND VENDOR_ID = @VENDOR_ID AND STATUS = 'Cancelled'

					SELECT @QTY_STARTED = SUM(Wafer_QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = (SELECT LOCATION_ID FROM LOCATION WHERE L_TYPE = 'FAB' AND VENDOR_ID = @VENDOR_ID )
									 AND PO_NUMBER = @PO_NO AND LOT_CURRENT_STATUS <> 'Close'

					SET @BALANCE_QTY = ISNULL(@Order_QTY,0) - ISNULL(@Shipped_QTY,0)-ISNULL(@Cancelled_Qty,0)-ISNULL(@QTY_STARTED,0)

					SELECT @POLYLAYERS = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Poly Layers'
					SELECT @EPITAXIAL = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Epitaxial'
					SELECT @NOOFMASKLAYERS = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = '# of Mask Layers'
					SELECT @MASKSERVICETYPE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Mask Service Type'
					SELECT @PROCESSGEOMETRY = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Process Geometry'
					SELECT @PROCESS = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Fab Process'
					SELECT @WaferSIZE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Wafer Size'
					SELECT @METALLAYERS = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Metal Layers'

					INSERT INTO #RESULTS ( [Vendor Name],[Part Number],[PO No.],[PO Date],[Ordered Qty],[Shipped Qty],[Cancelled Qty],[Qty Started],[Balance Qty],
											[PO Rate], [PO Amount],[Mask Service Type], [Process Geometry], [Process],	[Wafer Size (Inch)],[Metal Layers],	
											[Poly Layers],[Epitaxial],[# of Mask Layers] )
					VALUES (@V_NAME, @PART_NO, @PO_NO, @PO_DATE, @Order_QTY, @Shipped_QTY,@Cancelled_Qty,@QTY_STARTED,@BALANCE_QTY,@PO_COST, (@PO_COST*@Order_QTY),
							@MASKSERVICETYPE, @PROCESSGEOMETRY, @PROCESS, @WaferSIZE, @METALLAYERS, @POLYLAYERS, @EPITAXIAL, @NOOFMASKLAYERS)
					
				END
				
		SET @PO_ID			= NULL		
		SET @PROCESSSTEP_ID = NULL
		SET @VENDOR_ID		= NULL
		SET @V_NAME			= NULL
		SET @PO_NO			= NULL
		SET @PO_DATE		= NULL	
		SET @Order_QTY		= NULl
		SET @BALANCE_QTY	= NULL
		SET @QTY_STARTED	= NULL
		SET @Cancelled_Qty	= NULL
		SET @Shipped_QTY	= NULL
		SET @POLYLAYERS		= NULL
		SET @EPITAXIAL		= NULL
		SET @NOOFMASKLAYERS		= NULL
		SET @MASKSERVICETYPE		= NULL
		SET @METALLAYERS		= NULL
		SET @WaferSIZE		= NULL
		SET @PROCESSGEOMETRY		= NULL

		FETCH NEXT FROM @PO_CURSOR INTO @PO_ID , @PROCESSSTEP_ID, @VENDOR_ID,@Order_QTY
	END	
	CLOSE @PO_CURSOR
	DEALLOCATE @PO_CURSOR	

	SELECT [Vendor Name],[Part Number],[PO No.],CONVERT(VARCHAR, [PO Date], 101) AS [PO Date] ,[Ordered Qty],
		[Shipped Qty], [Cancelled Qty],	[Qty Started],[Balance Qty], [PO Rate], [PO Amount],
		[Mask Service Type], [Process Geometry], [Process],	[Wafer Size (Inch)],[Metal Layers],	
		[Poly Layers],[Epitaxial],[# of Mask Layers] FROM #RESULTS
END
